package com.surfilter.code.util;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import com.surfilter.code.model.ColumnData;
import com.surfilter.code.model.Field;
import com.surfilter.code.model.ForeignKey;
import com.surfilter.code.model.Index;
import com.surfilter.code.model.ModuleModel;
import com.surfilter.code.model.PK;
import com.surfilter.code.model.Table;
import com.surfilter.code.model.TableData;
import com.surfilter.dp.util.PropertyFileManager;

public class DaoTools {

	private static Properties dataSourceProp = new Properties();

	static {

//		try {
//			init();
//		} catch (IOException e) {
//			e.printStackTrace();
//		}
	}

	private static DaoTools instance;

	private DaoTools() {
	}

	public static DaoTools getInstance() {
		if (instance == null) {
			instance = new DaoTools();
		}
		return instance;
	}

	/**
	 * 获取模块信息
	 * 
	 * @throws Exception
	 */
	public List<ModuleModel> getModuleListByPattens(String pattern)
			throws Exception {
		List<ModuleModel> target = new ArrayList<ModuleModel>();

		StringBuffer condition = new StringBuffer();
		if (pattern.equals("*")) {
			// 全部
		} else {
			String[] moduleArray = pattern.split("\\|");
			condition.append("(");
			int i = 0;
			for (String moduleName : moduleArray) {
				if (i == 0) {
					condition.append("'" + moduleName + "'");
				} else {
					condition.append("," + "'" + moduleName + "'");
				}
				i++;
			}
			condition.append(")");
		}

		StringBuffer sql = new StringBuffer();

		if (condition != null && !condition.equals("")) {
			sql.append("SELECT * FROM PL_MODULE WHERE MODULE_NAME IN "
					+ condition);
		}
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery(sql.toString());
		System.out.println(sql.toString());
		while (rs.next()) {
			ModuleModel module = new ModuleModel();
			module.setId(rs.getInt("ID"));
			module.setParent(rs.getString("PNAME"));
			module.setModuleName(rs.getString("MODULE_NAME"));
			module.setModuleText(rs.getString("MODULE_TEXT"));
			module.setCodePath(rs.getString("CODE_PATH"));
			module.setTableName(rs.getString("TABLE_NAME"));
			module.setServiceName(rs.getString("SERVICE_NAME"));
			module.setServiceCls(rs.getString("SERVICE_CLS"));
			target.add(module);
		}
		return target;
	}

	private Connection conn;
	private DatabaseMetaData metaData;
	private Connection superConn;
	private Map<String, Connection> connMap = new HashMap<String, Connection>();
	
	/**
	 * 获取数据库连接
	 * 
	 * @param dataSource
	 * @return
	 * @throws Exception
	 */
	public void initSuperConnection(String dataSource) throws Exception {
		String jdbcUrl = dataSourceProp.getProperty(dataSource + ".jdbcUrl");
		String driverClassName = dataSourceProp.getProperty("driverClassName");
		String user = dataSourceProp.getProperty(dataSource + ".user");
		String pass = dataSourceProp.getProperty(dataSource + ".password");
		Class.forName(driverClassName);
//		superConn = DriverManager.getConnection(jdbcUrl, user, pass);
	}

	/**
	 * 获取数据库连接
	 * 
	 * @param dataSource
	 * @return
	 * @throws Exception
	 */
	public void initConnection(String dataSource) throws Exception {
		String jdbcUrl = PropertyFileManager.getProp("jdbcUrl");
		String user = PropertyFileManager.getProp("user");
		String pass = PropertyFileManager.getProp("password");
		conn = DriverManager.getConnection(jdbcUrl, user, pass);
		metaData = conn.getMetaData();
	}

	private static void init() throws IOException {
		InputStream in = DaoTools.class
				.getResourceAsStream("/config/properties/datasource.properties");
		dataSourceProp.load(in);
	}

	/**
	 * 获取表字段信息集合
	 * 
	 * @param tableName
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException 
	 */
	public List<Field> getFieldList(String tableName, String dataSource) throws SQLException, ClassNotFoundException {
		List<Field> target = new ArrayList<Field>();
		Connection conn = getConnectionByDataSource(dataSource);
		if(conn == null){
			return null;
		}
		
		ResultSet rs = conn.getMetaData().getColumns(null, null, tableName, null);
		while (rs.next()) {
			String col = rs.getString("COLUMN_NAME");
			int type = rs.getInt("DATA_TYPE");
			String comment = rs.getString("REMARKS");
			String typeName = rs.getString("TYPE_NAME");
			Class<?> o = this.types.get(type);
			if (type == 3 || type == 2) {
				int decimalDigit = rs.getInt("DECIMAL_DIGITS");
				int size = rs.getInt("COLUMN_SIZE");
				if (decimalDigit == 0) {
					if (size <= 9)
						o = Integer.class;
					else if (size <= 19)
						o = Long.class;
					else
						o = BigDecimal.class;
				} else if (decimalDigit > 0) {
					if (size <= 6)
						o = Float.class;
					else if (size < 15)
						o = Double.class;
					else
						o = BigDecimal.class;
				}
			} else if (type == 1111 || type == -3) {
				continue;
			}

			if (o == null)
				throw new RuntimeException(rs.getInt("DATA_TYPE") + ":"
						+ rs.getString("TYPE_NAME"));
			Field field = new Field();
			String def = rs.getString("COLUMN_DEF");
			if (def != null)
				def = def.trim();
			field.setDef(def);
			field.setName(col.toLowerCase());
			field.setType(o.getName());
			field.setLength(rs.getInt("COLUMN_SIZE"));
			field.setNullable(!rs.getString("IS_NULLABLE").equals("NO"));
			field.setComment(comment);
			field.setSqlTypeName(typeName);
			String autoIncrement = rs.getString("IS_AUTOINCREMENT");
			field.setAutoIncrement(autoIncrement.equals("YES"));
			target.add(field);

		}
		return target;
	}

	/**
	 * 获取指定表名主键
	 * 
	 * @param tableName
	 * @throws SQLException
	 * @throws ClassNotFoundException 
	 */
	public PK getPkByTableName(String tableName, String dataSource) throws SQLException, ClassNotFoundException {

		PK pk = new PK();
		
		Connection conn = getConnectionByDataSource(dataSource);
		if(conn == null){
			return null;
		}
		
		ResultSet rs = conn.getMetaData().getPrimaryKeys(null, null, tableName);
		
		List<Field> pkCols = new ArrayList<Field>();

		while (rs.next()) {
			pk.setName(rs.getString("PK_NAME"));
			String pkName = rs.getString("COLUMN_NAME");
			ResultSet trs = conn.getMetaData().getColumns(null, null, tableName, pkName);
			while (trs.next()) {
				String col = trs.getString("COLUMN_NAME");
				int type = trs.getInt("DATA_TYPE");
				String comment = trs.getString("REMARKS");
				Class<?> o = this.types.get(type);
				if (type == 3 || type == 2) {
					int decimalDigit = trs.getInt("DECIMAL_DIGITS");
					int size = trs.getInt("COLUMN_SIZE");
					if (decimalDigit == 0) {
						if (size <= 9)
							o = Integer.class;
						else if (size <= 19)
							o = Long.class;
						else
							o = BigDecimal.class;
					} else if (decimalDigit > 0) {
						if (size <= 6)
							o = Float.class;
						else if (size < 15)
							o = Double.class;
						else
							o = BigDecimal.class;
					}
				} else if (type == 1111 || type == -3) {
					continue;
				}

				if (o == null)
					throw new RuntimeException(trs.getInt("DATA_TYPE") + ":"
							+ trs.getString("TYPE_NAME"));
				Field field = new Field();
				String def = trs.getString("COLUMN_DEF");
				if (def != null)
					def = def.trim();
				field.setDef(def);
				field.setName(col.toLowerCase());
				field.setType(o.getName());
				field.setLength(trs.getInt("COLUMN_SIZE"));
				field.setNullable(!trs.getString("IS_NULLABLE").equals("NO"));
				field.setComment(comment);
				pkCols.add(field);
			}
			
		}
		pk.setCols(pkCols);
		return pk;
	}

	/**
	 * 获取指定表名的外键
	 * 
	 * @param tableName
	 * @throws SQLException
	 * @throws ClassNotFoundException 
	 */
	public List<ForeignKey> getFkByTableName(String tableName, String dataSource) throws SQLException, ClassNotFoundException {

		List<ForeignKey> target = new ArrayList<ForeignKey>();
		
		Connection conn = getConnectionByDataSource(dataSource);
		if(conn == null){
			return null;
		}
		
		ResultSet rs = conn.getMetaData().getImportedKeys(null, null, tableName);
		//key：外键名，value：外键对象
		Map<String, ForeignKey> foreignKeyMap = new HashMap<String, ForeignKey>();
		
        while (rs.next()) {
            
        	//外键名
            String fkName = rs.getString("FK_NAME");
            ForeignKey fk = new ForeignKey();
            if(!foreignKeyMap.containsKey(fkName)){
            	foreignKeyMap.put(fkName, fk);
            }else{
            	fk = foreignKeyMap.get(fkName);
            }
            
            fk.setName(fkName);
            List<String> columnList = fk.getColumnList();
            if(columnList == null){
            	columnList = new ArrayList<String>();
            	fk.setColumnList(columnList);
            }
            //当前表列名
            String fkColumnName = rs.getString("FKCOLUMN_NAME");
            columnList.add(fkColumnName);
            
            //被引用表名
            String fTable = rs.getString("PKTABLE_NAME");
            fk.setForeignTableName(fTable);
            
            //被引用表列名
            String pkColumnName = rs.getString("PKCOLUMN_NAME");
            List<String> foreginColumnList = fk.getForeignColumnList();
            if(foreginColumnList == null){
            	foreginColumnList = new ArrayList<String>();
            	fk.setForeignColumnList(foreginColumnList);
            }
            
            //数据更新规则
            String rule = getFkRule(rs.getShort("UPDATE_RULE"), rs.getShort("DELETE_RULE"));
            fk.setRule(rule);
            foreginColumnList.add(pkColumnName);
            
        }
        
        for(String key : foreignKeyMap.keySet()){
        	target.add(foreignKeyMap.get(key));
        }
        
        return target;
	}
	
	private String getFkRule(short updateRule, short deleteRule) {
		StringBuffer target = new StringBuffer(); 
		if(updateRule == 0){
			target.append(" ON UPDATE CASCADE ");
		}
		if(deleteRule == 0){
			target.append(" ON DELETE CASCADE ");
		}
		return target.toString();
	}

	/**
	 * 获取指定表信息
	 * @param tableName
	 * @throws SQLException
	 * @throws ClassNotFoundException 
	 */
	public Table getTableByTableName(String tableName, String dataSource) throws SQLException, ClassNotFoundException {
		
		Connection conn = getConnectionByDataSource(dataSource);
		if(conn == null){
			return null;
		}
		Table target = new Table();
		//仅匹配表名
		String sql = "SELECT TABLE_COMMENT AS REMARKS FROM information_schema.TABLES WHERE TABLE_NAME = '" + tableName + "'";
		
//		ResultSet rs = conn.getMetaData().getTables(null, null, tableName, new String[]{"TABLE"});
		ResultSet rs = conn.createStatement().executeQuery(sql);
		 
        while (rs.next()) {
        	String comment = rs.getString("REMARKS");
        	int index = comment.lastIndexOf("; InnoDB");
        	if(index != -1){
        		comment = comment.substring(0, index);
        	}
        	target.setComment(comment);
        	target.setTableName(tableName);
        }
        return target;
	}
	
	private Connection getConnectionByDataSource(String dataSource) throws ClassNotFoundException, SQLException {
		Connection target = null;
		if(connMap.containsKey(dataSource)){
			target = connMap.get(dataSource);
		}
		String jdbcUrl = PropertyFileManager.getProp("jdbcUrl");
		String user = PropertyFileManager.getProp("user");
		String pass = PropertyFileManager.getProp("password");
		Connection conn = DriverManager.getConnection(jdbcUrl, user, pass);
		connMap.put(dataSource, conn);
		target = connMap.get(dataSource);
		return target;
	}

	/**
	 * 获取指定表的索引信息
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 */
	public List<Index> getIndexByTableName(String tableName, String dataSource) throws SQLException, ClassNotFoundException{

		List<Index> target = new ArrayList<Index>();
		
		Map<String, Index> indexMap = new HashMap<String, Index>();
		
		Connection conn = getConnectionByDataSource(dataSource);
		if(conn == null){
			return null;
		}
		
		ResultSet rs = conn.getMetaData().getIndexInfo(null, null, tableName, false, false);
		
        while (rs.next()) {
            String indexName = rs.getString("INDEX_NAME");
            if(indexName == null) 
            	continue;
            
            Index index = new Index();
            if(!indexMap.containsKey(indexName)){
            	indexMap.put(indexName, index);
            }else{
            	index = indexMap.get(indexName);
            }
            List<String> columnList = index.getColumnList();
            if(columnList == null){
            	columnList = new ArrayList<String>();
            	index.setColumnList(columnList);
            }
            columnList.add(rs.getString("COLUMN_NAME"));
            index.setOrder(rs.getString("ASC_OR_DESC"));
            index.setUnique(!rs.getBoolean("NON_UNIQUE"));//唯一性约束
            index.setName(indexName);
        }
        
        
        for(String key : indexMap.keySet()){
        	target.add(indexMap.get(key));
        }
        return target;
	}
	
	public static void main(String[] args) throws Exception {

		init();
		getInstance().initConnection("surfilter");
		getInstance().getTableByTableName("pz_black", "surfilter");
//		getInstance().test("");
//		getInstance().get
		System.out.println("");
	}
	
	
	@SuppressWarnings("rawtypes")
	private Map<Integer, Class> types = new HashMap<Integer, Class>();
	{

		types.put(Integer.valueOf(Types.ARRAY), null);
		types.put(Integer.valueOf(Types.BIGINT), Long.class);
		types.put(Integer.valueOf(Types.BIT), Boolean.class);
		types.put(Integer.valueOf(Types.BLOB), Blob.class);
		types.put(Integer.valueOf(Types.BINARY), null);
		types.put(Integer.valueOf(Types.BOOLEAN), Boolean.class);
		types.put(Integer.valueOf(Types.CHAR), String.class);
		types.put(Integer.valueOf(Types.CLOB), Clob.class);
		types.put(Integer.valueOf(Types.DATALINK), null);
		types.put(Integer.valueOf(Types.DATE), java.util.Date.class);
		types.put(Integer.valueOf(Types.DECIMAL), Long.class);
		types.put(Integer.valueOf(Types.DISTINCT), null);
		types.put(Integer.valueOf(Types.DOUBLE), Double.class);
		types.put(Integer.valueOf(Types.FLOAT), Float.class);
		types.put(Integer.valueOf(Types.INTEGER), Integer.class);
		types.put(Integer.valueOf(Types.JAVA_OBJECT), null);
		types.put(Integer.valueOf(Types.LONGVARBINARY), Blob.class);
		types.put(Integer.valueOf(Types.LONGVARCHAR), String.class);
		types.put(Integer.valueOf(Types.NUMERIC), Long.class);
		types.put(Integer.valueOf(Types.OTHER), null);
		types.put(Integer.valueOf(Types.REAL), Double.class);
		types.put(Integer.valueOf(Types.REF), null);
		types.put(Integer.valueOf(Types.SMALLINT), Integer.class);
		types.put(Integer.valueOf(Types.STRUCT), null);
		types.put(Integer.valueOf(Types.TIME), Time.class);
		types.put(Integer.valueOf(Types.TIMESTAMP), java.util.Date.class);
		types.put(Integer.valueOf(Types.TINYINT), Integer.class);
		types.put(Integer.valueOf(Types.VARBINARY), null);
		types.put(Integer.valueOf(Types.VARCHAR), String.class);
	}
	
	/**
	 * 获取数据集合
	 * @param tableName
	 * @throws SQLException 
	 */
	public List<TableData> getTableDataList(String tableName, String condition) throws SQLException {
		
		List<TableData> target = new ArrayList<TableData>();
		
		String sql = "SELECT * FROM " + tableName + " WHERE " + condition;
		System.out.println(sql);
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql);
		ResultSetMetaData rsmeta = rs.getMetaData();
		int columnCount = rsmeta.getColumnCount();
		
		while(rs.next()){
			TableData tableData = new TableData();
			List<ColumnData> columnDataList = new ArrayList<ColumnData>();
			tableData.setTableName(tableName);
			
			for(int index = 1; index < columnCount + 1; index ++){
				Object obj = rs.getObject(index);
				int type = rsmeta.getColumnType(index);
				Class<?> o = this.types.get(type);
				ColumnData columnData = new ColumnData();
				
				columnData.setColumnName(rsmeta.getColumnName(index));
				columnData.setValue(obj);
				columnData.setType(o.getName());
				
				columnDataList.add(columnData);
			}
			tableData.setColumnData(columnDataList);
			target.add(tableData);
		}
		return target;
	}

	/**
	 * 获取模块信息 
	 * @throws SQLException 
	 */
	public ModuleModel getModuleById(int id) throws SQLException {
		Statement stmt = conn.createStatement();
		String sql = "SELECT * FROM pl_module WHERE ID = " + id;
		ResultSet rs = stmt.executeQuery(sql);
		ModuleModel module = null;
		while(rs.next()){
			module = new ModuleModel();
			module.setId(rs.getInt("ID"));
			module.setParent(rs.getString("PNAME"));
			module.setModuleName(rs.getString("MODULE_NAME"));
			module.setModuleText(rs.getString("MODULE_TEXT"));
			module.setCodePath(rs.getString("CODE_PATH"));
			module.setTableName(rs.getString("TABLE_NAME"));
			module.setServiceName(rs.getString("SERVICE_NAME"));
		}
	
		return module;
	}
	
	public ModuleModel getModuleByPname(String pname) throws SQLException {
		Statement stmt = conn.createStatement();
		String sql = "SELECT * FROM pl_module WHERE PNAME = " + pname;
		ResultSet rs = stmt.executeQuery(sql);
		ModuleModel module = null;
		while(rs.next()){
			module = new ModuleModel();
			module.setId(rs.getInt("ID"));
			module.setParent(rs.getString("PNAME"));
			module.setModuleName(rs.getString("MODULE_NAME"));
			module.setModuleText(rs.getString("MODULE_TEXT"));
			module.setCodePath(rs.getString("CODE_PATH"));
			module.setTableName(rs.getString("TABLE_NAME"));
			module.setServiceName(rs.getString("SERVICE_NAME"));
		}
	
		return module;
	}
	

	public Collection<? extends String> getActioListByModuleId(int id) throws SQLException {
		Set<String> target = new HashSet<String>();
		Statement stmt = conn.createStatement();
		String sql = "SELECT * FROM pl_action WHERE MODULE_ID = " + id;
		ResultSet rs = stmt.executeQuery(sql);
		while(rs.next()){
			target.add(rs.getString("ACTION_NAME"));
		}
		return target;
	}

	public String getModuleIdByName(String moduleName) throws SQLException {
		Statement stmt = conn.createStatement();
		String sql = "SELECT * FROM pl_module WHERE MODULE_NAME = '" + moduleName + "'";
		ResultSet rs = stmt.executeQuery(sql);
		String target = "";
		while(rs.next()){
			
			target = rs.getInt("ID") + "";
		}
	
		return target;
	}

	public String getActionIdByName(String actionName) throws SQLException {
		Statement stmt = conn.createStatement();
		String sql = "SELECT * FROM pl_action WHERE ACTION_NAME = '" + actionName + "'";
		ResultSet rs = stmt.executeQuery(sql);
		String target = "";
		while(rs.next()){
			
			target = rs.getInt("ID") + "";
		}
	
		return target;
	}

	public List<String> getTriggerByTableName(String tableName, String dataSource, String dstDialect) throws ClassNotFoundException, SQLException {
		
		List<String> target = new ArrayList<String>();
		if(dstDialect == null || !dstDialect.equals("mysql")){
			return target;
		}
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ");
		sql.append("information_schema ");
		sql.append(".");
		sql.append("TRIGGERS");
		sql.append(" WHERE TRIGGER_SCHEMA = '" + dataSource + "'");
		sql.append(" AND EVENT_OBJECT_SCHEMA = '" + dataSource + "'");
		sql.append(" AND EVENT_OBJECT_TABLE = '" + tableName + "'");
		

		Connection conn = getConnectionByDataSource(dataSource);
		if(conn == null){
			return null;
		}
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(sql.toString());
		
		while(rs.next()){
			String triggerName = rs.getString("TRIGGER_NAME");
			String actionTiming = rs.getString("ACTION_TIMING");
			String eventManipulation = rs.getString("EVENT_MANIPULATION");
			String actionOrientation = rs.getString("ACTION_ORIENTATION");
			String actionStatment = rs.getString("ACTION_STATEMENT");
			StringBuffer triggerSql = new StringBuffer();
			triggerSql.append("CREATE TRIGGER ");
			triggerSql.append("`" + triggerName + "`");
			triggerSql.append(" " + actionTiming + " ");
			triggerSql.append(" " + eventManipulation + " ");
			triggerSql.append(" ON ");
			triggerSql.append("`"+ tableName + "`");
			triggerSql.append(" FOR EACH " + actionOrientation + " ");
			triggerSql.append(actionStatment);
			target.add(triggerSql.toString());
		}
		return target;
	}
}
